package com.housekeeping.dao.impl;

import com.housekeeping.dao.OrderDAO;
import com.housekeeping.model.Order;
import com.housekeeping.utils.DBUtils;

import java.util.List;

public class OrderDAOImpl implements OrderDAO {

    @Override
    public Order findById(Integer orderId) {
        String sql = "SELECT order_id as orderId, order_no as orderNo, user_id as userId, service_id as serviceId, " +
                    "staff_id as staffId, service_date as serviceDate, service_time as serviceTime, address, " +
                    "contact, phone, amount, status, remark, create_time as createTime, update_time as updateTime " +
                    "FROM orders WHERE order_id = ?";
        return DBUtils.queryForObject(sql, Order.class, orderId);
    }

    @Override
    public List<Order> findAllWithDetails() {
        String sql = "SELECT o.order_id as orderId, o.order_no as orderNo, o.user_id as userId, o.service_id as serviceId, " +
                    "o.staff_id as staffId, o.service_date as serviceDate, o.service_time as serviceTime, o.address, " +
                    "o.contact, o.phone, o.amount, o.status, o.remark, o.create_time as createTime, o.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, st.real_name as staffName, " +
                    "CASE WHEN r.review_id IS NOT NULL THEN 1 ELSE 0 END as reviewed " +
                    "FROM orders o " +
                    "LEFT JOIN users u ON o.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON o.service_id = s.service_id " +
                    "LEFT JOIN users st ON o.staff_id = st.user_id " +
                    "LEFT JOIN reviews r ON o.order_id = r.order_id " +
                    "ORDER BY o.create_time DESC";
        return DBUtils.queryForList(sql, Order.class);
    }

    @Override
    public List<Order> findByUserId(Integer userId) {
        String sql = "SELECT o.order_id as orderId, o.order_no as orderNo, o.user_id as userId, o.service_id as serviceId, " +
                    "o.staff_id as staffId, o.service_date as serviceDate, o.service_time as serviceTime, o.address, " +
                    "o.contact, o.phone, o.amount, o.status, o.remark, o.create_time as createTime, o.update_time as updateTime, " +
                    "s.service_name as serviceName, st.real_name as staffName " +
                    "FROM orders o " +
                    "LEFT JOIN housekeeping_services s ON o.service_id = s.service_id " +
                    "LEFT JOIN users st ON o.staff_id = st.user_id " +
                    "WHERE o.user_id = ? ORDER BY o.create_time DESC";
        return DBUtils.queryForList(sql, Order.class, userId);
    }

    @Override
    public List<Order> findByStaffId(Integer staffId) {
        String sql = "SELECT o.order_id as orderId, o.order_no as orderNo, o.user_id as userId, o.service_id as serviceId, " +
                    "o.staff_id as staffId, o.service_date as serviceDate, o.service_time as serviceTime, o.address, " +
                    "o.contact, o.phone, o.amount, o.status, o.remark, o.create_time as createTime, o.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName " +
                    "FROM orders o " +
                    "LEFT JOIN users u ON o.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON o.service_id = s.service_id " +
                    "WHERE o.staff_id = ? ORDER BY o.create_time DESC";
        return DBUtils.queryForList(sql, Order.class, staffId);
    }

    @Override
    public List<Order> findByStatus(Integer status) {
        String sql = "SELECT o.order_id as orderId, o.order_no as orderNo, o.user_id as userId, o.service_id as serviceId, " +
                    "o.staff_id as staffId, o.service_date as serviceDate, o.service_time as serviceTime, o.address, " +
                    "o.contact, o.phone, o.amount, o.status, o.remark, o.create_time as createTime, o.update_time as updateTime, " +
                    "u.real_name as userName, s.service_name as serviceName, st.real_name as staffName " +
                    "FROM orders o " +
                    "LEFT JOIN users u ON o.user_id = u.user_id " +
                    "LEFT JOIN housekeeping_services s ON o.service_id = s.service_id " +
                    "LEFT JOIN users st ON o.staff_id = st.user_id " +
                    "WHERE o.status = ? ORDER BY o.create_time DESC";
        return DBUtils.queryForList(sql, Order.class, status);
    }

    @Override
    public int insert(Order order) {
        String sql = "INSERT INTO orders (order_no, user_id, service_id, service_date, " +
                    "service_time, address, contact, phone, amount, status, remark) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return DBUtils.update(sql, order.getOrderNo(), order.getUserId(), 
                order.getServiceId(), order.getServiceDate(), order.getServiceTime(),
                order.getAddress(), order.getContact(), order.getPhone(),
                order.getAmount(), order.getStatus(), order.getRemark());
    }

    @Override
    public int update(Order order) {
        String sql = "UPDATE orders SET service_date = ?, service_time = ?, " +
                    "address = ?, contact = ?, phone = ?, amount = ?, " +
                    "status = ?, remark = ?, update_time = NOW() WHERE order_id = ?";
        return DBUtils.update(sql, order.getServiceDate(), order.getServiceTime(),
                order.getAddress(), order.getContact(), order.getPhone(),
                order.getAmount(), order.getStatus(), order.getRemark(), order.getOrderId());
    }

    @Override
    public int updateStatus(Integer orderId, Integer status) {
        String sql = "UPDATE orders SET status = ?, update_time = NOW() WHERE order_id = ?";
        return DBUtils.update(sql, status, orderId);
    }

    @Override
    public int assignStaff(Integer orderId, Integer staffId) {
        String sql = "UPDATE orders SET staff_id = ?, update_time = NOW() WHERE order_id = ?";
        return DBUtils.update(sql, staffId, orderId);
    }

    @Override
    public int delete(Integer orderId) {
        String sql = "DELETE FROM orders WHERE order_id = ?";
        return DBUtils.update(sql, orderId);
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM orders";
        Object result = DBUtils.queryForScalar(sql);
        return result != null ? ((Number) result).longValue() : 0;
    }
    
    @Override
    public int countByStaffId(Integer staffId) {
        String sql = "SELECT COUNT(*) FROM orders WHERE staff_id = ?";
        Object result = DBUtils.queryForScalar(sql, staffId);
        return result != null ? ((Number) result).intValue() : 0;
    }
    
    @Override
    public int countByStaffIdAndStatus(Integer staffId, Integer status) {
        String sql = "SELECT COUNT(*) FROM orders WHERE staff_id = ? AND status = ?";
        Object result = DBUtils.queryForScalar(sql, staffId, status);
        return result != null ? ((Number) result).intValue() : 0;
    }
} 